import pymysql



def get_table_names(host, port, user, password, database):
    try:
        # 连接数据库
        connection = pymysql.connect(host=host, port=port, user=user, password=password, database=database)

        # 创建游标对象
        cursor = connection.cursor()

        # 获取所有表名
        cursor.execute("SHOW TABLES")

        # 提取表名列表
        tables = cursor.fetchall()
        table_names = [table[0] for table in tables]

        return table_names

    except pymysql.Error as e:
        print(f"Error while connecting to MySQL: {e}")
        return []

    finally:
        # 关闭游标和数据库连接
        if cursor:
            cursor.close()
        if connection:
            connection.close()

def generate_alter_table_sql(table_name, alterations):
    # table_name: 要修改的表名
    # alterations: 包含要进行的修改操作的列表，每个操作为一个字符串 SQL 语句

    sql_statements = []

    for alteration in alterations:
        sql = f"ALTER TABLE {table_name} {alteration};"
        sql_statements.append(sql)

    return sql_statements


# MySQL数据库连接配置
host = '192.168.10.137'
port = 3306
user = 'root'
password = '123456'
database = 'know_sub'

# 获取所有表名
table_names = get_table_names(host, port, user, password, database)
print("Tables in the database:", table_names)

# 假设要给每个表添加相同的字段
alterations = [
    "ADD create_user_id VARCHAR(64) NULL COMMENT '创建人ID'",
    "ADD create_time DATETIME DEFAULT CURRENT_TIMESTAMP NULL COMMENT '创建时间'",
    "ADD update_user_id VARCHAR(64) NULL COMMENT '更新人'",
    "ADD update_time DATETIME DEFAULT CURRENT_TIMESTAMP NULL COMMENT '更新时间'"
]

# 生成修改语句
print('-- -----')
for table_name in table_names:
    alter_sqls = generate_alter_table_sql(table_name, alterations)
    for sql in alter_sqls:
        print(sql)
    print('-- -----')
